package com.g2room.search.es.biz;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.g2room.tools.db.db2po.DataSourceModel;
import com.g2room.tools.db.db2po.DbConstant;
import com.g2room.utils.StringUtil;

public class InitPeeMapping {
	private static DataSourceModel ds;
	/** 排除不映射的表 */
	private static List<String> excpList = new ArrayList<String>();
	private static List<String> anaList = new ArrayList<String>();
	static {
		ds = new DataSourceModel();
		ds.setDriver("com.mysql.jdbc.Driver");
		ds.setUrl("jdbc:mysql://127.0.0.1:3306/myuall?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull");
//		ds.setUsername("op");
//		ds.setPassword("!wlms*yts_asw");
		ds.setUsername("root");
		ds.setPassword("123456");
		
		excpList.add("grap_err");
		
		excpList.add("sys_user");
		excpList.add("sys_set");
		excpList.add("univ_info");
		excpList.add("tog_cat_mapping");
		excpList.add("ncee_arti_category");
		excpList.add("univ_img_type");
//		excpList.add("univ_property");
//		excpList.add("univ_img_type");
//		excpList.add("univ_arti_set");
		anaList.add("name");
		anaList.add("title");
		anaList.add("content");
		anaList.add("summary");
		anaList.add("major_name");
		anaList.add("specialty_name");
		anaList.add("school_name");
		anaList.add("faculties");
		anaList.add("teacher_name");
//		anaMap.put("major_info", "name");
//		anaMap.put("ncee_arti", "title,content");
//		anaMap.put("tog_images", "title");
//		anaMap.put("tog_post", "title,summary,content");
//		anaMap.put("univ_image", "school_name");
//		anaMap.put("univ_info", "name");
//		anaMap.put("univ_enro_new", "school_name");
//		anaMap.put("univ_enrollment", "title,school_name");
//		anaMap.put("univ_judgement", "school_name");
//		anaMap.put("univ_key_major", "school_name");
//		anaMap.put("univ_major", "school_name,name,title");
//		anaMap.put("univ_major_score", "school_name,specialty_name");
//		anaMap.put("univ_prov_score", "school_name");
//		anaMap.put("univ_rank", "school_name");
//		anaMap.put("univ_teacher", "school_name,faculties,teacher_name");
	}
	
	public static void main(String[] args) {
//		createPo("pee_univ_major");
//		createPos();
		createMapping();
	}
	

	public static void createPos() {
		InitPeeMapping ti = new InitPeeMapping();
		Map<String, String> tbs = ti.getTablesByStatus();
		StringBuffer sb = new StringBuffer();
		StringBuffer ifSb = new StringBuffer();
		StringBuffer filedSb = new StringBuffer();
		StringBuffer paramSb = new StringBuffer();
		StringBuffer indexTypeSb = new StringBuffer();
		for(String tn : tbs.keySet()) {
			if(excpList.contains(tn)) continue;
			Map<String, String> colus = ti.getColumns(tn, true);
			indexTypeSb.append(tn.toUpperCase() + "(\""+StringUtil.hump4Underline(tn)+"\"),\n");
			for(String col : colus.keySet()) {
				String colType = colus.get(col);
				String[] colAndcom = col.split(" ");
				if(colAndcom.length > 1) {
					sb.append("/** " +colAndcom[1]+"*/\n");
				}
				filedSb.append(colAndcom[0] + ",");
				paramSb.append("?,");
				String dbField = StringUtil.hump4Underline(colAndcom[0]);
				sb.append("private " + colType + " " + dbField +";\n");
				/**
				 * <if test="t.title != null and t.title != ''"> and title like CONCAT('%',#{t.title},'%')  </if>
        	<if test="t.type != null"> and type=#{t.type} </if>
        	<if test="t.position != null"> and position=#{t.position} </if>
			<if test="t.imageUrl != null"> and imageUrl=#{t.imageUrl} </if>
			<if test="t.status != null"> and status=#{t.status} </if>
			<if test="t.province != null"> and province=#{t.province}</if>
			<if test="t.city != null "> and city=#{t.city} </if>
				 */
				if(colType.equals("String") || colType.equals("Date")) {
					ifSb.append("<if test=\"t."+dbField+" != null and t."+dbField+" != ''\"> and "+colAndcom[0]+"=#{t."+dbField+"} </if>\n");
				} else {
					ifSb.append("<if test=\"t."+dbField+" != null\"> and "+colAndcom[0]+"=#{t."+dbField+"} </if>\n");
				}
//				sb.append("private " + colType + " " +colAndcom[0]+";\n");
//				sb.append("private " + colType + " " +colAndcom[0]+";\n");
			}
		}
//		System.out.println(filedSb.toString());
//		System.out.println("insert into "+ tn +"("+filedSb.toString()+") values("+paramSb.toString()+")");
		
//		System.out.println(filedSb.toString() + "\n\n");
//		System.out.println(sb.toString());
//		System.out.println(ifSb.toString());
		System.out.println(indexTypeSb.toString());
	
	}
	public static void createPo(String tn) {
		InitPeeMapping ti = new InitPeeMapping();
		StringBuffer sb = new StringBuffer();
		StringBuffer ifSb = new StringBuffer();
		StringBuffer filedSb = new StringBuffer();
		StringBuffer paramSb = new StringBuffer();
//		if(excpList.contains(tn)) return;
		Map<String, String> colus = ti.getColumns(tn, true);
		for(String col : colus.keySet()) {
			String colType = colus.get(col);
			String[] colAndcom = col.split(" ");
			if(colAndcom.length > 1) {
				sb.append("/** " +colAndcom[1]+"*/\n");
			}
			if(!colAndcom[0].equals("id")) {
			filedSb.append(colAndcom[0] + ",");
			paramSb.append("?,");
			}
			String dbField = StringUtil.hump4Underline(colAndcom[0]);
			sb.append("private " + colType + " " + dbField +";\n");
			/**
			 * <if test="t.title != null and t.title != ''"> and title like CONCAT('%',#{t.title},'%')  </if>
    	<if test="t.type != null"> and type=#{t.type} </if>
    	<if test="t.position != null"> and position=#{t.position} </if>
		<if test="t.imageUrl != null"> and imageUrl=#{t.imageUrl} </if>
		<if test="t.status != null"> and status=#{t.status} </if>
		<if test="t.province != null"> and province=#{t.province}</if>
		<if test="t.city != null "> and city=#{t.city} </if>
			 */
			if(colType.equals("String") || colType.equals("Date")) {
				ifSb.append("<if test=\"t."+dbField+" != null and t."+dbField+" != ''\"> and "+colAndcom[0]+"=#{t."+dbField+"} </if>\n");
			} else {
				ifSb.append("<if test=\"t."+dbField+" != null\"> and "+colAndcom[0]+"=#{t."+dbField+"} </if>\n");
			}
//				sb.append("private " + colType + " " +colAndcom[0]+";\n");
//				sb.append("private " + colType + " " +colAndcom[0]+";\n");
		}
//		System.out.println(filedSb.toString());
		System.out.println("insert into "+ tn +"("+filedSb.toString()+") values("+paramSb.toString()+")");
		
//		System.out.println(filedSb.toString() + "\n\n");
//		System.out.println(sb.toString());
//		System.out.println(ifSb.toString());
		
	}
	
	public static void createMapping() {
		InitPeeMapping ti = new InitPeeMapping();
		Map<String, String> tbs = ti.getTablesByStatus();
		Map<String, String> fieldMap = new HashMap<String, String>();
		StringBuffer sb = new StringBuffer();
		StringBuffer tnSb = new StringBuffer();
		tnSb.append("List<String> tns = new ArrayList<String>();");
//		sb.append("Map<String, XContentBuilder> builderMap = new HashMap<String, XContentBuilder>();\n");
		for(String tn : tbs.keySet()) {
			// 排除
			if(excpList.contains(tn)) continue;
//			if(!tn.equals("univ_image_cover")) continue;
			tnSb.append("tns.add(\""+ tn+"\");\n");
			
			Map<String, String> colus = ti.getColumns(tn, false);
			sb.append("builderMap.put(\""+StringUtil.hump4Underline(tn)+"\", XContentFactory.jsonBuilder().startObject()");
			
			sb.append(".startObject(\""+StringUtil.hump4Underline(tn)+"\").startObject(\"properties\")");
			for(String col : colus.keySet()) {
				String colType = colus.get(col);
				
//				// 注释掉部分为检验字段类型是否一致，不一致这无法创建mapping
//				String fieldType = fieldMap.get(col);
//				if(null == fieldType) {
//					fieldMap.put(col, tn+"@"+colType);
//				}else if(null != fieldType && !fieldType.contains(colType)) {
//					System.out.println(col + ", fieldType=" + fieldType+", colType=" + tn+"@"+colType);
//				}
			
				sb.append(".startObject(\""+StringUtil.hump4Underline(col)+"\")");
				if(colType.equals(DbConstant.FIELD_TYPE_INT) || colType.equals(DbConstant.FIELD_TYPE_BIGDECIMAL)
						|| colType.equals(DbConstant.FIELD_TYPE_BYTE)
						|| colType.equals(DbConstant.FIELD_TYPE_DOUBLE)
						|| colType.equals(DbConstant.FIELD_TYPE_FLOAT)
						|| colType.equals(DbConstant.FIELD_TYPE_LONG)
						|| colType.equals(DbConstant.FIELD_TYPE_SHORT)
						) {
					if(colType.equals(DbConstant.FIELD_TYPE_BIGDECIMAL)) colType = DbConstant.FIELD_TYPE_INT;
					if(colType.equals(DbConstant.FIELD_TYPE_DOUBLE)) colType = DbConstant.FIELD_TYPE_FLOAT;
					sb.append(".field(\"type\", \""+colType.toLowerCase()+"\")");
//					sb.append(".field(\"index\",\"not_analyzed\")");
				} else if(colType.equals(DbConstant.FIELD_TYPE_DATE)
						|| colType.equals(DbConstant.FIELD_TYPE_DATETIME)
						){
					sb.append(".field(\"format\",\"yyy-MM-dd"+(colType.equals(DbConstant.FIELD_TYPE_DATETIME) ? " HH:mm:ss" :"")+"||epoch_millis\")");
					sb.append(".field(\"type\", \""+DbConstant.FIELD_TYPE_DATE.toLowerCase()+"\")");
				} else {
					sb.append(".field(\"type\", \"string\")");
					// 判断是否分词
					if(anaList.contains(col)) {
						sb.append(".field(\"index\",\"analyzed\")");
						sb.append(".field(\"analyzer\",\"ik\")");
					} else {
						sb.append(".field(\"index\",\"not_analyzed\")");
					}
				}
				sb.append(".endObject()");
			}
			sb.append(".endObject().endObject().endObject()");
			sb.append(");\n");
		}
//		System.out.println(tnSb.toString());
		System.out.println(sb.toString());
	}
	
		/**
		 * 从数据源中获取字段名称及字段类型
		 * 
		 * @param ds 数据源配置Model，详见{@link:DataSourceModel}
		 * @param tableName 要获取字段的数据表名称
		 * @return Map<String, String>类型，key值为字段名，value为字段类型
		 * @auther <a href="mailto:g2room@163.com">天涯</a> Jul 28, 2013 4:11:07 PM
		 */
		public Map<String, String> getColumns(String tableName, boolean comment) {
			Map<String, String> columns = null;
			Connection conn = null;
			ResultSet rs = null;
			try {
				conn = getConn(ds);
				DatabaseMetaData metaData = conn.getMetaData();
				rs = metaData.getColumns(null, null, tableName, "");
				rs.last();
				columns = new HashMap<String, String>(rs.getRow());
				rs.first();
				columns.put(rs.getString("COLUMN_NAME")
						+ (comment ? " " + rs.getString("REMARKS") : ""),
						DbConstant.MAP_JDBC_JAVA_TYPE.get(rs
								.getInt("DATA_TYPE")));
			    while(rs.next()) {
			    	// COLUMN_NAME：字段名称
			    	// DATA_TYPE：字段数据类型(对应java.sql.Types中的常量)
			    	// TYPE_NAME：字段数据类型名称
			    	columns.put(rs.getString("COLUMN_NAME")
							+ (comment ? " " + rs.getString("REMARKS") : ""),
							DbConstant.MAP_JDBC_JAVA_TYPE.get(rs.getInt("DATA_TYPE")));
			    }
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if(null != rs) rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				closeConn(conn);
			}
			return columns;
		}
		
		/**
		 * 获取配置连接的所有数据库
		 *
		 * @param ds 数据源配置Model，详见{@link:DataSourceModel}
		 * @return 数据库名的List列表
		 * @auther <a href="mailto:g2room@163.com">天涯</a>
		 * Jul 28, 2013 12:29:34 PM
		 */
		public List<String> getDbName(){
			List<String> dbNames = new ArrayList<String>();
			Connection conn = null;
			ResultSet rs = null;
			try {
				conn = getConn(ds);
				DatabaseMetaData metaData = conn.getMetaData();
				rs = metaData.getCatalogs();
				rs.last();
				dbNames = new ArrayList<String>(rs.getRow());
				rs.first();
				dbNames.add(rs.getString("TABLE_CAT"));
			    while(rs.next()) {
			    	dbNames.add(rs.getString("TABLE_CAT"));
			    }
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if(null != rs) rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				closeConn(conn);
			}
			return dbNames;
		}
		public Map<String, String> getTablesByStatus() {
			// 记录表数据，key为表名，值为表注释
				Connection conn = null;
				ResultSet rs = null;
				Statement statement = null;
				Map<String, String> tables = new HashMap<String, String>();
				try {
					conn = getConn(ds);
					//SHOW TABLE STATUS
					String querySql = "SHOW TABLE STATUS";
					statement = conn.createStatement();
					rs = statement.executeQuery(querySql);
					while (rs.next()) {
						tables.put(rs.getString("Name"), rs.getString("Comment"));
					}
				}  catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						if(null != rs) rs.close();
						if(null != statement) statement.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					closeConn(conn);
				}
				return tables;
		}
		/**
		 * 获取所有的表名
		 *
		 * @param ds 数据源配置Model，详见{@link:DataSourceModel}
		 * @return 表名的List列表
		 * @auther <a href="mailto:g2room@163.com">天涯</a>
		 * Jul 28, 2013 12:26:22 PM
		 */
		public Map<String, String> getTables() {
			// 记录表数据，key为表名，值为表注释
			Connection conn = null;
			ResultSet rs = null;
			Map<String, String> tables = new HashMap<String, String>();
			try {
				conn = getConn(ds);
				DatabaseMetaData metaData = conn.getMetaData();
				/**
				 	第一个是数据库名称，对于MySQL，则对应相应的数据库，对于Oracle来说，则是对应相应的数据库实例，可以不填，也可以直接使用Connection的实例对象中的getCatalog()方法返回的值填充；
					第二个是模式，可以理解为数据库的登录名，而对于Oracle也可以理解成对该数据库操作的所有者的登录名。对于Oracle要特别注意，其登陆名必须是大写，不然的话是无法获取到相应的数据，而MySQL则不做强制要求。
					第三个是表名称，一般情况下如果要获取所有的表的话，可以直接设置为null，如果设置为特定的表名称，则返回该表的具体信息。
					第四个是类型标准,以数组形式传值，有"TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM"这几个经典的类型，一般使用”TABLE”，即获取所有类型为TABLE的表
					
					返回一个ResultSet对象，有10列，详细的显示了表的类型：
						TABLE_CAT String => 表类别（可为 null）
						TABLE_SCHEM String => 表模式（可为 null）
						TABLE_NAME String => 表名称
						TABLE_TYPE String => 表类型。
						REMARKS String => 表的解释性注释
						TYPE_CAT String => 类型的类别（可为 null）
						TYPE_SCHEM String => 类型模式（可为 null）
						TYPE_NAME String => 类型名称（可为 null）
						SELF_REFERENCING_COL_NAME String => 有类型表的指定 "identifier" 列的名称（可为 null）
						REF_GENERATION String
				 */
				rs = metaData.getTables(conn.getCatalog(),null,null,new String[]{"TABLE"});
	//			rs.last();
	//			tables = new ArrayList<String>(rs.getRow());
				rs.first();
				tables.put(rs.getString("TABLE_NAME"), rs.getString("REMARKS"));
			    while(rs.next()) {
			    	tables.put(rs.getString("TABLE_NAME"), rs.getString("REMARKS"));
			    }
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if(null != rs) rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				closeConn(conn);
			}
			return tables;
		}
		/**
		 * 获取数据库连接对象，这里只获得，需要自己在使用后进行关闭。
		 *
		 * @return 数据库连接实例
		 * @throws ClassNotFoundException 
		 * @throws SQLException 
		 * @auther <a href="mailto:g2room@163.com">天涯</a>
		 * Jul 6, 2013 8:49:25 PM
		 */
		public Connection getConn(DataSourceModel dataSource)
				throws ClassNotFoundException, SQLException {
			// 1.加载驱动
			Class.forName(dataSource.getDriver());
			// 2.得到连接
			return DriverManager.getConnection(dataSource.getUrl(), dataSource
					.getUsername(), dataSource.getPassword());
		}
		
		/**
		 * 
		 * 关闭数据库连接。
		 *
		 * @param conn 数据库连接实例
		 * @auther <a href="mailto:g2room@163.com">天涯</a>
		 * Jul 11, 2013 5:06:44 PM
		 */
		public static void closeConn(Connection conn) {
			if(null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
}

